﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using Common;
using CNCSoft.EData;
using System.Data.SqlClient;

using DevExpress.XtraGrid.Views.Grid;


namespace CNCSoft.ErpApps.TMS.Academic
{
    public partial class frmQuanLyLop : Form
    {
        public frmQuanLyLop()
        {
            InitializeComponent();
        }

        DataSet ds = new DataSet();
        DataTable DM_KhoaHoc_Ready;

        private void frmQuanLyLop_Load(object sender, EventArgs e)
        {
            ds.Tables.Add("DS_HOCSINH");

            //So lieu DM cho may Combo
            try
            {
                DM_KhoaHoc_Ready = new DataTable();
                SQLHelper.Fill(DM_KhoaHoc_Ready, GlobalVariable.WSInfo.ConnectionString, "SELECT * FROM DM_KHOAHOC WHERE TrangThai = 'Ready'");
                cboKhoaHoc.Properties.DataSource = DM_KhoaHoc_Ready;
                cboKhoaHoc.Properties.DisplayMember = "TenKhoaHoc";
                cboKhoaHoc.Properties.ValueMember = "MaKhoaHoc";

                colTen.SortOrder = DevExpress.Data.ColumnSortOrder.Ascending;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void cboKhoaHoc_EditValueChanged(object sender, EventArgs e)
        {
            string strMaLop = cboKhoaHoc.EditValue.ToString();
            
            //Load thong tin Lop (cac label tren vung master)
            GetInformationForBatch(strMaLop);
            
            //Load danh sach hoc sinh cua lop
            GetData_DSLop(strMaLop);
        }

        //Ham nay dung de dua danh sach hic sinh vao Lop 
        private void GetData_DSLop(string MaLop)
        {
            try
            {
                if (ds.Tables["DS_HOCSINH"] != null)
                        ds.Tables["DS_HOCSINH"].Clear();
                SQLHelper.Fill(ds.Tables["DS_HOCSINH"], GlobalVariable.WSInfo.ConnectionString,
                                                        "SELECT ID, INVOICE_KHOAHOC.MaKhoaHoc, INVOICE_KHOAHOC.InvoiceNumber, Holot, Ten, NickName, DienthoaiDidong, Invoice.Counsellor, Amount, ThongTinThem " + 
                                                        "FROM INVOICE_KHOAHOC " +
                                                        "JOIN Invoice ON INVOICE_KHOAHOC.InvoiceNumber = Invoice.InvoiceNumber " + 
                                                        "JOIN Enquiry ON Invoice.EnquiryNumber = Enquiry.EnquiryNumber " + 
                                                        "WHERE MaKhoaHoc = '" +  MaLop + "'"); 

                gridDSHocSinh.DataSource = ds;
                gridDSHocSinh.DataMember = "DS_HOCSINH";

                            }
            catch (SqlException ex)
            {
                MessageBox.Show("Error: \n" + ex.Message);
            }
        }

        //Ham nay dung de lay thong tin cua Lop de dua vao cac label
        private void GetInformationForBatch(string makhoahoc)
        {
            try
            {
                string strSQL = "SELECT	[TenNgayTrongTuan] AS NgayHoc,[TenGiohoc] AS GioHoc, [SoGioHoc] AS TongGioHoc,[SoNgayHoc] AS TongNgayHoc, " +
                                "[NgayBatDau],[NgayKetThuc],[DoanhThu],[NgayMoLop]  " +
                                "FROM [dbo].[DM_KHOAHOC] AS A " +
                                "LEFT JOIN [dbo].[DM_NGAYTRONGTUAN] AS B ON A.MaNgayTrongTuan = B.MaNgayTrongTuan " +
                                "LEFT JOIN [dbo].[DM_GIOHOC] AS C ON A.MaTimeSlot = C.MaGiohoc " +
                                "WHERE MaKhoaHoc='" + makhoahoc + "'";
                SqlDataReader dr = SQLHelper.ExecuteReader(GlobalVariable.WSInfo.ConnectionString, strSQL);
                while (dr.Read())
                {
                    lblNgayHoc.Text = dr["NgayHoc"].ToString();
                    lblTongNgayHoc.Text = dr["TongNgayHoc"].ToString();
                    lblGioHoc.Text = dr["GioHoc"].ToString();
                    lblTongGioHoc.Text = dr["TongGioHoc"].ToString();
                    lblNgayBatDau.Text = String.Format("{0:dd/MM/yyyy}", dr["NgayBatDau"]);
                    lblNgayKetThuc.Text = String.Format("{0:dd/MM/yyyy}", dr["NgayKetThuc"]);
                    lblDoanhThu.Text = String.Format("{0:0,000}", dr["DoanhThu"]);
                    lblNgayMoLop.Text = String.Format("{0:dd/MM/yyyy}", dr["NgayMoLop"]);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Loi load thong tin khoa hoc");
            }
        }

        //Event nay dung de load danh sach HV can chon de them vao lop
        private void gridViewDataInAvailableClass_InitNewRow(object sender, DevExpress.XtraGrid.Views.Grid.InitNewRowEventArgs e)
        {
            //Load Danh sach hoc sinh da enroll nhung chua hoc lop nao (de chon bo sung)
            frmView fv = new frmView("SELECT Invoice.InvoiceNumber, HoLot, Ten, DienthoaiDidong, MaLop, InvoiceDate, StartDate, Invoice.Counsellor, InvoiceDetail.SoTien AS SoTien " +
                                     "FROM Enquiry " + 
                                      "JOIN Invoice ON Enquiry.EnquiryNumber = Invoice.EnquiryNumber " +
                                      "JOIN InvoiceDetail ON Invoice.InvoiceNumber = InvoiceDetail.InvoiceNumber " +
                                      "WHERE InvoiceDetail.InvoiceNumber NOT IN (SELECT InvoiceNumber FROM Invoice_KhoaHoc) ",
                                      "EnquiryDate");
            fv.ShowDialog(this);

            if (fv.FlChoose)  //Da chon Enroll tu form fv
            {
                //Gan cac gia tri tu form da chon vao datatable "DS_HOCSINH"  (di nhien la thong qua grid)
                DevExpress.XtraGrid.Views.Grid.GridView view = sender as DevExpress.XtraGrid.Views.Grid.GridView; 

                //Gan gia tri cho field InvoiceNumber
                string invoiceNumber = fv.SelectedIDs.Replace("('", "").Replace("')", "").ToString();
                view.SetRowCellValue(e.RowHandle, view.Columns["InvoiceNumber"], invoiceNumber);

                //Gan gia tri cho field MaKhoaHoc
                view.SetRowCellValue(e.RowHandle, view.Columns["MaKhoaHoc"], cboKhoaHoc.EditValue.ToString());

                //Cac truong khac phai lay tu Enquiry va Invoice  (may field nay ko luu)
                IDataReader dr = SQLHelper.ExecuteReader(GlobalVariable.WSInfo.ConnectionString, "SELECT Holot, Ten, DienthoaiDidong, Invoice.Counsellor, Amount, ThongTinThem FROM Invoice JOIN Enquiry ON Invoice.EnquiryNumber = Enquiry.EnquiryNumber " +
                                                                                                 "WHERE Invoice.InvoiceNumber = '" + invoiceNumber + "'");
                while (dr.Read())
                {
                    try
                    {
                        view.SetRowCellValue(e.RowHandle, view.Columns["Holot"], dr["Holot"]);
                        view.SetRowCellValue(e.RowHandle, view.Columns["Ten"], dr["Ten"]);
                        view.SetRowCellValue(e.RowHandle, view.Columns["DienthoaiDidong"], dr["DienthoaiDiDong"]);
                        view.SetRowCellValue(e.RowHandle, view.Columns["Counsellor"], dr["Counsellor"]);
                        view.SetRowCellValue(e.RowHandle, view.Columns["Amount"], dr["Amount"]);
                        view.SetRowCellValue(e.RowHandle, view.Columns["ThongTinThem"], dr["ThongTinThem"]);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }

            }
        }

        //Cap nhat du lieu (DS Lop)
        private void cmdCapNhat_Click(object sender, EventArgs e)
        {
            SqlConnection cnn = new SqlConnection(GlobalVariable.WSInfo.ConnectionString);

            string sqlInsert, sqlUpdate, sqlDelete;
            sqlInsert = "INSERT INTO INVOICE_KHOAHOC(MaKhoaHoc, InvoiceNumber, NickName) " +
                                              " VALUES(@pMaKhoaHoc, @pInvoiceNumber, @pNickName )";

            sqlUpdate = "UPDATE INVOICE_KHOAHOC SET MaKhoaHoc = @pMaKhoaHoc, InvoiceNumber = @pInvoiceNumber, NickName = @pNickName " +
                        " WHERE ID = @pID";

            sqlDelete = "DELETE FROM INVOICE_KHOAHOC WHERE ID = @pID";

            SqlParameter[] pInsert = new SqlParameter[3];
            SqlParameter[] pUpdate = new SqlParameter[4];
            SqlParameter[] pDelete = new SqlParameter[1];

            pInsert[0] = new SqlParameter("@pMaKhoaHoc", SqlDbType.VarChar, 50, "MaKhoaHoc");
            pInsert[1] = new SqlParameter("@pInvoiceNumber", SqlDbType.VarChar, 12, "InvoiceNumber");
            pInsert[2] = new SqlParameter("@pNickName", SqlDbType.NVarChar, 50, "NickName");
            
            pUpdate[0] = new SqlParameter("@pMaKhoaHoc", SqlDbType.VarChar, 50, "MaKhoaHoc");
            pUpdate[1] = new SqlParameter("@pInvoiceNumber", SqlDbType.VarChar, 12, "InvoiceNumber");
            pUpdate[2] = new SqlParameter("@pNickName", SqlDbType.NVarChar, 50, "NickName");
            pUpdate[3] = new SqlParameter("@pID", SqlDbType.Int, 18, "ID");
            
            pDelete[0] = new SqlParameter("@pID", SqlDbType.Int, 18, "ID");

            SqlCommand cmdInsert = new SqlCommand(sqlInsert, cnn);
            SqlCommand cmdUpdate = new SqlCommand(sqlUpdate, cnn);
            SqlCommand cmdDelete = new SqlCommand(sqlDelete, cnn);

            cmdInsert.Parameters.AddRange(pInsert);
            cmdUpdate.Parameters.AddRange(pUpdate);
            cmdDelete.Parameters.AddRange(pDelete);

            SqlDataAdapter da = new SqlDataAdapter();
            da.InsertCommand = cmdInsert;
            da.UpdateCommand = cmdUpdate;
            da.DeleteCommand = cmdDelete;
            try
            {
                da.Update(ds, "DS_HOCSINH");
                ds.AcceptChanges();
                MessageBox.Show("Cập nhật thành công danh sách học viên lớp " + cboKhoaHoc.EditValue.ToString(), "Cập nhật", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        //Bo qua, load lai DS lop
        private void cmdBoQua_Click(object sender, EventArgs e)
        {
            GetData_DSLop(cboKhoaHoc.EditValue.ToString());
        }

        //Cap nhat lai thong tin cua mot lop (trong DM_KHOAHOC)
        private void btnEditLop_Click(object sender, EventArgs e)
        {
            if (cboKhoaHoc.EditValue == null) return;
 
            try
            {
                if (cboKhoaHoc.EditValue.ToString() != "")
                {
                    string makhoahoc = cboKhoaHoc.EditValue.ToString();
                    longGiaoVu.frmEditKhoaHoc form_edit_khoahoc = new longGiaoVu.frmEditKhoaHoc(makhoahoc);
                    form_edit_khoahoc.ShowDialog();

                    //Sau khi cao nhat xong thi load lai thong tin cua Lop
                    if (form_edit_khoahoc.DialogResult ==  System.Windows.Forms.DialogResult.OK)
                        GetInformationForBatch(makhoahoc);
                }
                else
                {
                    MessageBox.Show("Xin mời bạn chọn lớp để chỉnh sửa");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


    }
}
